<!---

entries.cfc
- add (text, date - yyyy-mm-dd)
- delete (id)
- edit (id, text, date)
- moveUp (id)
- moveDown (id)
- getEntriesList (start) rt: query  [DONE]
- countDays () rt: integer  [DONE]
- getText (id) rt: string  [DONE]

--->
<cfcomponent>

    <cffunction name="add">
        <cfargument name="text" type="string" required="true">
        <cfargument name="date" type="string" required="true">

		<!--- Try to get day --->
       	<cfquery name="value" datasource="#Request.DS#">
			SELECT `id` FROM `cfp_days` WHERE `date` like "#date#%"
				AND `user_id` = '#Session.id#'
		</cfquery>
		<cfif value.id EQ '' >
			<!--- Day is not available --->
			<!--- Get Week ID, otherwise create week --->
       		<cfquery name="valueWeek" datasource="#Request.DS#">
				SELECT `id` FROM `cfp_weeks` WHERE YEARWEEK(`cfp_weeks`.`date`) = YEARWEEK('#date# 00:00:00')
					AND `user_id` = '#Session.id#'
			</cfquery>
			<cfif valueWeek.id EQ '' >
       			<cfquery datasource="#Request.DS#">
					INSERT INTO `cfp_weeks` SET
						`user_id` = '#Session.id#',
						`date` = '#date# 00:00:00',
						`text` = ''
				</cfquery>
			</cfif>

			<!--- Create missing Day --->
        	<cfquery name="valueDay" datasource="#Request.DS#">
				INSERT INTO `cfp_days` SET
					`user_id` = '#Session.id#',
					`date` = "#date# 00:00:00",
					`week_id` = (SELECT `cfp_weeks`.`id` FROM `cfp_weeks` WHERE
						YEARWEEK(`cfp_weeks`.`date`) = YEARWEEK('#date# 00:00:00') AND `user_id` = '#Session.id#' LIMIT 1 )
			</cfquery>
		</cfif>

		<!--- Create entry --->
		<cfquery name="maxSort" datasource="#Request.DS#">
			SELECT max(`sort_id`) as `max` FROM `cfp_entries`
				WHERE `day_id` = (SELECT `id` FROM `cfp_days` WHERE `date` = '#date# 00:00:00' AND `user_id` = '#Session.id#' LIMIT 1 )
					AND `user_id` = '#Session.id#'
		</cfquery>
		<cfif maxSort.max EQ ''><cfset maxSort.max = 0 ></cfif>
      	<cfquery datasource="#Request.DS#">
			INSERT INTO `cfp_entries` SET
				`user_id` = '#Session.id#',
				`day_id` = (SELECT `id` FROM `cfp_days` WHERE `date` = '#date# 00:00:00' AND `user_id` = '#Session.id#' LIMIT 1 ),
				`text` = '#text#',
				`sort_id` = '#maxSort.max+1#'
		</cfquery>
    </cffunction>


	<cffunction name="delete">
        <cfargument name="id" type="numeric" required="true">
		
		<!--- If there are empty days: delete it --->
       	<cfquery name="value" datasource="#Request.DS#">
			SELECT `day_id`, `sort_id` FROM `cfp_entries` WHERE `id` = "#id#" AND `user_id` = '#Session.id#'
		</cfquery>
		<cf_emptyDays day_id="#value.day_id#">

		<!--- Delete entry --->
        <cfquery datasource="#Request.DS#">
			DELETE FROM `cfp_entries` WHERE `id` = '#id#' AND `user_id` = '#Session.id#'
		</cfquery>

		<!--- Adjust sort_id --->
		<cfquery datasource="#Request.DS#">
			UPDATE `cfp_entries` SET
			`sort_id` = `sort_id` - 1
			WHERE `day_id` = '#value.day_id#' AND `sort_id` > '#value.sort_id#' AND `user_id` = '#Session.id#'
		</cfquery>
	</cffunction>

    <cffunction name="edit">
        <cfargument name="id" type="numeric" required="true">
        <cfargument name="text" type="string">
        <cfargument name="date" type="string">

		<cfif IsDefined('text') >
        	<cfquery datasource="#Request.DS#">
				UPDATE `cfp_entries` SET `text` = '#text#' WHERE `id` = '#id#' AND `user_id` = '#Session.id#'
			</cfquery>
		</cfif>

       	<cfquery name="currentDate" datasource="#Request.DS#">
			SELECT `date` FROM `cfp_days` WHERE
				`id` = (SELECT `day_id` FROM `cfp_entries` WHERE `id` = "#id#" AND `user_id` = '#Session.id#') AND `user_id` = '#Session.id#'
		</cfquery>
		<cfif IsDefined('date') AND DateFormat(currentDate.date, 'yyyy-mm-dd') NEQ date >

			<!--- If there are empty day, than delete it--->
       		<cfquery name="value" datasource="#Request.DS#">
				SELECT `day_id` FROM `cfp_entries` WHERE `id` = "#id#" AND `user_id` = '#Session.id#'
			</cfquery>
			<cf_emptyDays day_id="#value.day_id#">

        	<!--- Try to get day --->
	       	<cfquery name="value" datasource="#Request.DS#">
				SELECT `id` FROM `cfp_days` WHERE `date` like "#date#%" AND `user_id` = '#Session.id#'
			</cfquery>
			<cfif !IsDefined('value.id') >
				<!--- Day is not available --->
				<!--- Get Week ID, otherwise create week --->
	       		<cfquery name="valueWeek" datasource="#Request.DS#">
					SELECT `id` FROM `cfp_weeks` WHERE YEARWEEK(`cfp_weeks`.`date`) = YEARWEEK('#date# 00:00:00') AND `user_id` = '#Session.id#'
				</cfquery>
				<cfif !IsDefined('valueWeek.id') >
	       			<cfquery datasource="#Request.DS#">
						INSERT INTO `cfp_weeks` SET `user_id` = '#Session.id#', `date` = '#date# 00:00:00', `text` = ''
					</cfquery>
				</cfif>
	
				<!--- Create missing Day --->
	        	<cfquery name="valueDay" datasource="#Request.DS#">
					INSERT INTO `cfp_days` SET
						`date` = '#date# 00:00:00',
						`user_id` = '#Session.id#',
						`week_id` = (SELECT `cfp_weeks`.`id` FROM `cfp_weeks` WHERE
							YEARWEEK(`cfp_weeks`.`date`) = YEARWEEK('#date# 00:00:00') AND `user_id` = '#Session.id#' LIMIT 1 )
				</cfquery>
			</cfif>
				
			<!--- Update entry --->
			<cfquery name="maxSort" datasource="#Request.DS#">
				SELECT max(`sort_id`) as `max` FROM `cfp_entries`
					WHERE `day_id` = (SELECT `id` FROM `cfp_days` WHERE `date` = '#date# 00:00:00' AND `user_id` = '#Session.id#' LIMIT 1 ) AND `user_id` = '#Session.id#'
			</cfquery>
			<cfif maxSort.max EQ ''><cfset maxSort.max = 0 ></cfif>
       		<cfquery datasource="#Request.DS#">
				UPDATE `cfp_entries` SET
				`day_id` = (SELECT `id` FROM `cfp_days` WHERE `date` = '#date# 00:00:00' LIMIT 1 ), 
				`sort_id` = '#maxSort.max+1#'
				WHERE `id` = '#id#' AND `user_id` = '#Session.id#'
			</cfquery>
		</cfif>
    </cffunction>

    <cffunction name="move"> 
         <cfargument name="id" type="numeric" required="true"> 
         <cfargument name="position" type="numeric" required="true"> 

        <cfquery datasource="#Request.DS#">
			UPDATE `cfp_entries` SET `sort_id` = '#position#' WHERE `id` = '#id#' AND `user_id` = '#Session.id#'
	 	</cfquery>

    </cffunction>

    <cffunction name="getEntriesList"> 
        <cfargument name="start" type="numeric" required="true">

		<cfquery name="minEntries" datasource="#Request.DS#">
			SELECT
				(SELECT count(*) as `counted` FROM `cfp_entries`
				WHERE `cfp_entries`.`day_id` = `cfp_days`.`id`)
					as `counted`
			FROM `cfp_days` WHERE `user_id` = '#Session.id#'
			ORDER BY `date` DESC LIMIT 0,#start#
		</cfquery>
		<cfset minsum = 0>
		<cfoutput query="minEntries"><cfset minsum = minsum+minEntries.counted ></cfoutput>

		<cfquery name="maxEntries" datasource="#Request.DS#">
			SELECT
				(SELECT count(*) as `counted` FROM `cfp_entries`
				WHERE `day_id` = `cfp_days`.`id`)
					as `counted`
			FROM `cfp_days` WHERE `user_id` = '#Session.id#'
			ORDER BY `date` DESC LIMIT #start#,#Request.maxEntries#
		</cfquery>
		<cfset sum = 0>
		<cfoutput query="maxEntries"><cfset sum = sum+maxEntries.counted ></cfoutput>

		<cfquery name="list" datasource="#Request.DS#">
			SELECT `cfp_days`.`date` as `date`,
				 `cfp_days`.`school` as `school`,
				`cfp_entries`.`text` as `text`,
				`cfp_entries`.`id` as `id`,
				`cfp_entries`.`day_id` as `day_id`,
				`cfp_entries`.`sort_id` as `sort_id`
			FROM `cfp_entries`, `cfp_days`
			WHERE `cfp_days`.`id` = `cfp_entries`.`day_id`
				AND `cfp_entries`.`user_id` = '#Session.id#'
				AND `cfp_days`.`user_id` = '#Session.id#'
			ORDER BY `cfp_days`.`date` DESC, `cfp_entries`.`sort_id` ASC
			LIMIT #minsum#,#sum#
		 </cfquery>

		 <cfreturn list >
    </cffunction>

    <cffunction name="countDays"> 
         <cfquery name="value" datasource="#Request.DS#">
			SELECT count(*) as `counted` FROM `cfp_days` WHERE `user_id` = '#Session.id#'
		 </cfquery>
		 <cfreturn value.counted >
    </cffunction>

    <cffunction name="setSchool"> 
	<cfargument name="id" type="numeric" required="true">
	<cfargument name="value" type="numeric" required="true">
         <cfquery name="result" datasource="#Request.DS#">
			UPDATE `cfp_days` SET `school` = '#value#' WHERE `user_id` = '#Session.id#' AND `id` = '#id#'
		</cfquery>
    </cffunction>

    <cffunction name="addtmp">
	<cfargument name="text" required="true">
         <cfquery name="result" datasource="#Request.DS#">
			INSERT INTO `cfp_templates` SET `text` = '#text#', `user_id` = '#Session.id#'
		</cfquery>
    </cffunction>

    <cffunction name="deletetmp">
	<cfargument name="text" required="true">
         <cfquery name="result" datasource="#Request.DS#">
			DELETE FROM `cfp_templates` WHERE `text` = '#text#' AND `user_id` = '#Session.id#'
		</cfquery>
    </cffunction>

    <cffunction name="listtmp">
         <cfquery name="result" datasource="#Request.DS#">
		SELECT * FROM `cfp_templates` WHERE `user_id` = '#Session.id#' ORDER BY `id` DESC
	</cfquery>
	<cfreturn result >
    </cffunction>

</cfcomponent>